from flask  import Flask
from flask_sqlalchemy   import SQLAlchemy
import config

# 安装: pip install flask-sqlalchemy
'''
多对多:
1. 多对多的关系,要通过一个中间表进行关联.
2. 中间表,不能通过`class`的方式实现,只能通过`db.Table`的方式实现.
3. 设置关联: `tags = db.relationship('Tag',secondary=article_tag,backref=db.backref('articles'))` 需要使用一个关键字参数`secondary=中间表` 来进行关联. 
4. 访问和数据添加可以通过以下方式进行操作:

'''

app = Flask(__name__)
app.config.update({
    'DEBUG': True,
    'TEMPLATES_AUTO_RELOAD': True
})

# 1. 使用flask_sqlalchemy中的SQLAlchemy进行初始化.
app.config.from_object(config)
db = SQLAlchemy(app)

'''
create table article(
    id int primary key autoincrement,
    title varchar(100) not null,
)

create table tag (
    id int primary key autoincrement,
    name varchar(50) not null,
)

create table article_tag (
    article_id int,
    tag_id int,
    primary key('article_id','tag_id'),
    foreign key `article_id` references `article.id`,
    foreign key `tag_id` references `tag.id`,
)
'''
article_tag = db.Table('article_tag',
        db.Column('article_id',db.Integer,db.ForeignKey('article.id'),primary_key=True),
        db.Column('tag_id',db.Integer,db.ForeignKey('tag.id'),primary_key=True)
    )


class Article(db.Model):
    __tablename__='article'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    title = db.Column(db.String(100),nullable=False)

    tags = db.relationship('Tag',secondary=article_tag,backref=db.backref('articles'))

class Tag(db.Model):
    __tablename__='tag'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    name = db.Column(db.String(100),nullable=False)


# 3. 测试连接数据库是否正常.
db.create_all()


@app.route('/')
def index():
    return 'flask-sqlalchemy多对多关系'

@app.route('/Addarticle/')
def Addarti():

    article1 = Article(title='aaa')
    article2 = Article(title='bbb')

    tag1 = Tag(name='111')
    tag2 = Tag(name='222')
    
    article1.tags.append(tag1)
    article1.tags.append(tag2)

    article2.tags.append(tag1)
    article2.tags.append(tag2)

    db.session.add(article1)
    db.session.add(article2)

    db.session.add(tag1)
    db.session.add(tag2)

    db.session.commit()

    return '添加文章和标签成功.'


@app.route('/Selectarti/')
def SeleArti():
    article1 = Article.query.filter(Article.title == 'aaa').first()
    tags = article1.tags 
    for tag in tags:
        print(tag.name)
    return "查询数据成功"


if __name__ == "__main__":
    app.run(debug=True)
